use rusqlite::{Connection, Result};
use std::collections::HashMap;

#[derive(Debug)]
struct Person {
    id: i32,
    name: String,
    data: Option<Vec<u8>>,
}

// https://github.com/rusqlite/rusqlite
#[test]
fn demo() -> Result<()> {
    let conn = Connection::open_in_memory()?;

    // 创建表
    conn.execute(
        "
        CREATE TABLE person (
            id   INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            data BLOB)
            ",
        (),
    )?;

    let me = Person {
        id: 0,
        name: "张三".to_string(),
        data: None,
    };

    // 插入记录
    conn.execute(
        "INSERT INTO person(name, data) VALUES (?1, ?2)",
        (&me.name, &me.data),
    )?;

    // 查询记录
    let mut stmt = conn.prepare("SELECT id, name, data FROM person")?;
    let person_iter = stmt.query_map([], |row| {
        Ok(Person {
            id: row.get(0)?,
            name: row.get(1)?,
            data: row.get(2)?,
        })
    })?;

    for person in person_iter {
        println!("Found Person: {:?}", person?);
    }
    Ok(())
}

// 创建SQLite数据库
#[test]
fn create_db() -> Result<()> {
    let conn = Connection::open("cats.db")?;
    conn.execute(
        "create table if not exists cat_colors (
                id integer primary key,
                name text not null unique
             )",
        (),
    )?;

    conn.execute(
        "create table if not exists cats (
                id integer primary key,
                name text not null unique,
                color_id integer not null references cat_colors(id)
             )",
        (),
    )?;
    Ok(())
}

// 数据插入和查询
#[derive(Debug)]
struct Cat {
    name: String,
    color: String,
}

#[test]
fn insert_select() -> Result<()> {
    let conn = Connection::open("cats.db")?;

    let mut cat_colors = HashMap::new();
    cat_colors.insert(String::from("Blue"), vec!["Tigger", "Sammy"]);
    cat_colors.insert(String::from("Black"), vec!["Oreo", "Biscuit"]);

    for (color, cat_names) in &cat_colors {
        conn.execute("insert into cat_colors(name) values(?1)", [color])?;
        let last_id = conn.last_insert_rowid();
        for cat in cat_names {
            conn.execute(
                "insert into cats(name, color_id) values (?1, ?2)",
                (cat, last_id),
            )?;
        }
    }

    let mut stmt = conn.prepare(
        "
        select c.name, cc.name
          from cats      c
   inner join cat_colors cc
           on cc.id = c.color_id",
    )?;

    let cats = stmt.query_map([], |row| {
        Ok(Cat {
            name: row.get(0)?,
            color: row.get(1)?,
        })
    })?;

    for cat in cats {
        println!("Found Cat: {:?}", cat);
    }

    Ok(())
}
fn main() {}
